package th.co.exat.uap.model;

import com.channgam.dbmlib.sql.MySQLJDBC;
import com.channgam.dbmlib.sql.iDataAdapter;
import java.util.ArrayList;
import java.util.List;
import th.co.exat.uap.config.Config;

public class GovData {

    private static iDataAdapter i;
    private static int numRows;
    private static String sqlNumRows;
    private String carType;
    private String plate1;
    private String plate2;
    private String province;
    private String brand;
    private String color;
    private String ownerPrefix;
    private String ownerFname;
    private String ownerLname;
    private String ownerAddress;
    private String ownerDistric1;
    private String ownerDistric2;
    private String ownerProvince;
    private String plateFull;
    private String type;

    public static GovData checkData(String plateNo, String provinceName,
            String brandName, String colorName) throws Exception {
        GovData g = new GovData();

        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        String sql = "SELECT * "
                + "FROM uap_mst_gov "
                + "WHERE plate_full = '" + plateNo + "' "
                + "AND province = '" + provinceName + "' ";
        i.query(sql);

        if (i.resultNext()) {
            g.setPlateFull(i.result().getString("plate_full"));
            g.setProvince(i.result().getString("province"));
            g.setBrand(i.result().getString("brand"));
            g.setColor(i.result().getString("color"));
            g.setOwnerPrefix(i.result().getString("owner_prefix"));
            g.setOwnerFname(i.result().getString("owner_fname"));
            g.setOwnerLname(i.result().getString("owner_lname"));
            g.setOwnerAddress(i.result().getString("owner_address"));
            g.setOwnerDistric1(i.result().getString("owner_distric1"));
            g.setOwnerDistric2(i.result().getString("owner_distric2"));
            g.setOwnerProvince(i.result().getString("owner_province"));

            if (!colorName.equals(i.result().getString("color"))
                    || !brandName.equals(i.result().getString("brand"))) {

                g.setType("M");
            } else {
                g.setType("T");
            }

        } else {
            g.setType("N");
        }

        i.close();
        return g;
    }

    public static int checkData(String plateNo, String provinceName) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        /* check data do or not */
        String sql = "SELECT COUNT(*) AS C FROM uap_mst_gov "
                + "WHERE plate_full = '" + plateNo + "' "
                + "AND province = '" + provinceName + "' ";

        i.query(sql);
        i.resultNext();

        return i.result().getInt("C");
    }

    public static int insertData(GovData g) throws Exception {
        int ex = 0;
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        String sql = "INSERT INTO uap_mst_gov "
                + "(plate1, plate2, province, brand, color," +
                "   owner_prefix, owner_fname, owner_lname," +
                "   owner_address, owner_distric1, owner_distric2," +
                "   owner_province, plate_full) "
                + "VALUES "
                + "('" + g.getPlate1() + "'," +
                "'" + g.getPlate2() + "'," +
                "'" + g.getProvince() + "'," +
                "'" + g.getBrand() + "'," +
                "'" + g.getColor() + "'," +
                "'" + g.getOwnerPrefix() + "'," +
                "'" + g.getOwnerFname() + "'," +
                "'" + g.getOwnerLname() + "'," +
                "'" + g.getOwnerAddress() + "'," +
                "'" + g.getOwnerDistric1() + "'," +
                "'" + g.getOwnerDistric2() + "'," +
                "'" + g.getOwnerProvince() + "'," +
                "'" + g.getPlateFull() + "')";

        i.statement(sql);
        ex = i.execute();
        i.close();
        return ex;
    }

    public static int updateData(String plateNo, String provinceName, GovData g) throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        String sql = "UPDATE uap_mst_gov "
                + "SET "
                + "owner_prefix = '" + g.getOwnerPrefix() + "' ,"
                + "owner_fname = '" + g.getOwnerFname() + "' ,"
                + "owner_lname = '" + g.getOwnerLname() + "' ,"
                + "owner_address = '" + g.getOwnerAddress() + "' ,"
                + "owner_distric1 = '" + g.getOwnerDistric1() + "' ,"
                + "owner_distric2 = '" + g.getOwnerDistric2() + "' ,"
                + "owner_province = '" + g.getOwnerProvince() + "' "
                + "WHERE plate_full = '" + plateNo + "' "
                + "AND province = '" + provinceName + "' ";

        i.statement(sql);
        return i.execute();
    }

    public static List<GovData> getDataList(GovData g,
            int showRecord, int page) throws Exception {
        List<GovData> l = new ArrayList<GovData>();

        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        int gotoPage = 0;
        if (page <= 0) {
            page = 1;
        }

        gotoPage = (page - 1) * showRecord;
        String sql = "SELECT * "
                + "FROM uap_mst_gov "
                + "WHERE 1 ";

        String sqlNum = "SELECT COUNT(*) AS c "
                + "FROM uap_mst_gov "
                + "WHERE 1 ";
        if (g.getPlateFull() != null) {
            sql += "AND plate_full LIKE '%" + g.getPlateFull() + "%' ";
            sqlNum += "AND plate_full LIKE '%" + g.getPlateFull() + "%' ";
        }

        if (g.getProvince() != null) {
            sql += "AND province = '" + g.getProvince() + "' ";
            sqlNum += "AND province = '" + g.getProvince() + "' ";
        }

        GovData.sqlNumRows = sqlNum;

        sql += " ORDER BY plate_full ";
        sql += "LIMIT " + gotoPage + " , " + showRecord;

        i.query(sql);

        while (i.resultNext()) {
            GovData gd = new GovData();
            gd.setCarType(i.result().getString("car_type"));
            gd.setPlateFull(i.result().getString("plate_full"));
            gd.setProvince(i.result().getString("province"));
            gd.setBrand(i.result().getString("brand"));
            gd.setColor(i.result().getString("color"));
            gd.setOwnerPrefix(i.result().getString("owner_prefix"));
            gd.setOwnerFname(i.result().getString("owner_fname"));
            gd.setOwnerLname(i.result().getString("owner_lname"));
            l.add(gd);
        }

        i.close();
        return l;
    }

    public static int getNumRows() throws Exception {
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();

        i.query(GovData.sqlNumRows);
        while (i.resultNext()) {
            numRows = i.result().getInt("c");
        }

        i.close();

        return numRows;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getCarType() {
        return carType;
    }

    public void setCarType(String carType) {
        this.carType = carType;
    }

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public String getOwnerAddress() {
        return ownerAddress;
    }

    public void setOwnerAddress(String ownerAddress) {
        this.ownerAddress = ownerAddress;
    }

    public String getOwnerDistric1() {
        return ownerDistric1;
    }

    public void setOwnerDistric1(String ownerDistric1) {
        this.ownerDistric1 = ownerDistric1;
    }

    public String getOwnerDistric2() {
        return ownerDistric2;
    }

    public void setOwnerDistric2(String ownerDistric2) {
        this.ownerDistric2 = ownerDistric2;
    }

    public String getOwnerFname() {
        return ownerFname;
    }

    public void setOwnerFname(String ownerFname) {
        this.ownerFname = ownerFname;
    }

    public String getOwnerLname() {
        return ownerLname;
    }

    public void setOwnerLname(String ownerLname) {
        this.ownerLname = ownerLname;
    }

    public String getOwnerPrefix() {
        return ownerPrefix;
    }

    public void setOwnerPrefix(String ownerPrefix) {
        this.ownerPrefix = ownerPrefix;
    }

    public String getOwnerProvince() {
        return ownerProvince;
    }

    public void setOwnerProvince(String ownerProvince) {
        this.ownerProvince = ownerProvince;
    }

    public String getPlate1() {
        return plate1;
    }

    public void setPlate1(String plate1) {
        this.plate1 = plate1;
    }

    public String getPlate2() {
        return plate2;
    }

    public void setPlate2(String plate2) {
        this.plate2 = plate2;
    }

    public String getPlateFull() {
        return plateFull;
    }

    public void setPlateFull(String plateFull) {
        this.plateFull = plateFull;
    }

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }
}
